#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2019/5/23 9:35
# @Author : Mr.L
# @File : account_detail.py

import sys

import pandas as pd

import datetime, time

import logging

logging.basicConfig()
LOG = logging.getLogger('toexcel')
LOG.setLevel(logging.DEBUG)
# 创建一个handler，用于写入日志文件
fh = logging.FileHandler('toexcel.log')
fh.setLevel(logging.DEBUG)
# 定义handler的输出格式
LOG_FORMAT = logging.Formatter('%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
fh.setFormatter(LOG_FORMAT)
# 给logger添加handler
LOG.addHandler(fh)

# 再创建一个handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
ch.setFormatter(LOG_FORMAT)
LOG.addHandler(ch)

import numpy as np
import xlrd
import xlwt

sys.path.append('/data/online/youjia_script/com/youjia/util')
# sys.path.append('D:/youjia201811/youjia_report_new/com/youjia/util')
import calendar

from send_util import sendBigEmail

from db_config import get_config_13, get_config_test_69, get_config_13_1


def chongzhi():
    return '''
    SELECT CONCAT(p.user_id,'') 用户id,u.realname 姓名,  

    CASE p.pay_state WHEN 0 THEN '未支付'
    WHEN 1 THEN '已支付'
    ELSE '' END 支付状态,
    
    p.pay_amount/100 支付金额,
    p.gift_amount/100 赠送金额,concat(p.pay_activity_id,'') 用户充值活动ID,a.remark 活动说明,
    CONCAT(p.inviter_user_id,'') 邀请者的用户ID,
    u1.realname 邀请者,p.create_time 创建时间
    
    FROM user_account_pay_record p
    LEFT JOIN users u ON p.user_id=u.id
    LEFT JOIN users u1 ON u1.id=p.inviter_user_id
    LEFT JOIN user_account_pay_activity a ON p.pay_activity_id=a.id
    WHERE p.user_id IS NOT NULL
    AND p.pay_state=1
    AND DATE(p.create_time) BETWEEN DATE_SUB( CURDATE(),INTERVAL 7 day)  AND DATE_SUB( CURDATE(),INTERVAL 1 day);
    '''

def cost():
    return '''
    SELECT CONCAT(r.user_id,'') 用户id,u.realname 姓名,CONCAT(r.order_id,'') 订单id,r.cost_amount/100 消费金额 ,
        CASE r.cost_type WHEN 10 THEN '订单返现余额'
        WHEN 20 THEN '储值余额'
        WHEN 30 THEN '赠金余额'
        WHEN 40 THEN '第三方支付'
        ELSE '' END 消费金额类型,
        r.remark 备注,r.create_time 创建时间,
        (
                                    CASE
                                      b.pay_state
                                      WHEN '0'
                                      THEN '待支付'
                                      WHEN '1'
                                      THEN '已支付'
                                      ELSE ''
                                    END
                                  ) 支付状态,
                    (
                                    CASE
                                      b.order_type
                                      WHEN '0'
                                      THEN '分销'
                                      WHEN '1'
                                      THEN '自建'
                                            WHEN '2'
                                      THEN '有家'
                                      ELSE ''
                                    END
                                  ) 订单类型,
                                
                                 CASE b.cancel_status 
                                                WHEN 0  THEN '未取消'
                                        WHEN 1  THEN '房客取消（支付超时）'
                                        WHEN 2 THEN '房客取消（未付款）'
                                        WHEN 3 THEN '房客取消（全额退款）'
                                        WHEN 4 THEN '房客取消（部分扣款）'
                                        WHEN 5 THEN '房客取消（全额扣款）'
                                        WHEN 6 THEN '管家拒绝（订单取消）'
                                        WHEN 7 THEN '管家拒绝（全额退款）'
                                        WHEN 8 THEN '管家拒绝（确认超时）'
                                        WHEN 9 THEN '无房了'
                                        WHEN 10 THEN '客服介入'
                                        else
                                        ''
                                        end 取消状态,r.create_time 创建时间
        
        
        FROM user_account_cost_record r
        LEFT JOIN orders b ON r.order_id=b.id
        LEFT JOIN users u ON r.user_id=u.id
        WHERE r.user_id is NOT NULL
        AND r.order_id IS NOT NULL
        AND r.create_time BETWEEN DATE_SUB( CURDATE(),INTERVAL 7 day) AND  DATE_SUB( CURDATE(),INTERVAL 1 day)
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.order_channel=4
        AND b.city_id<>376
        AND b.brush_status=0
    '''

def refund():
    return '''
    SELECT
        CONCAT(r.user_id,'') 用户id,CONCAT(r.order_id,'') 订单id,
        r.refund_amount/100 退款金额,
        CASE r.refund_type WHEN 10 THEN '订单返现余额'
        WHEN 20 THEN '储值余额'
        WHEN 30 THEN '赠金余额'
        WHEN 40 THEN '第三方支付'
        ELSE '' END 消费金额类型,r.remark 备注 ,r.create_time  创建时间,
        
        (
                                    CASE
                                      b.pay_state
                                      WHEN '0'
                                      THEN '待支付'
                                      WHEN '1'
                                      THEN '已支付'
                                      ELSE ''
                                    END
                                  ) 支付状态,
                    (
                                    CASE
                                      b.order_type
                                      WHEN '0'
                                      THEN '分销'
                                      WHEN '1'
                                      THEN '自建'
                                            WHEN '2'
                                      THEN '有家'
                                      ELSE ''
                                    END
                                  ) 订单类型,
                                
                                 CASE b.cancel_status 
                                                WHEN 0  THEN '未取消'
                                        WHEN 1  THEN '房客取消（支付超时）'
                                        WHEN 2 THEN '房客取消（未付款）'
                                        WHEN 3 THEN '房客取消（全额退款）'
                                        WHEN 4 THEN '房客取消（部分扣款）'
                                        WHEN 5 THEN '房客取消（全额扣款）'
                                        WHEN 6 THEN '管家拒绝（订单取消）'
                                        WHEN 7 THEN '管家拒绝（全额退款）'
                                        WHEN 8 THEN '管家拒绝（确认超时）'
                                        WHEN 9 THEN '无房了'
                                        WHEN 10 THEN '客服介入'
                                        else
                                        ''
                                        end 取消状态,CONCAT(b.cancel_time,'') 取消时间
        FROM user_account_refund_record r
        LEFT JOIN orders b ON r.order_id=b.id
        WHERE r.user_id IS NOT NULL
        AND r.order_id IS NOT NULL
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.order_channel=4
        AND b.city_id<>376
        AND b.brush_status=0
        AND r.create_time BETWEEN DATE_SUB( CURDATE(),INTERVAL 7 day) AND  DATE_SUB( CURDATE(),INTERVAL 1 day)
    '''

def free():
    return '''
       SELECT CONCAT(t.user_id,'') 用户id,u.realname 姓名,u.mobile 电话,
        t.pay_remain_amount/100 储值支付余额,
        t.gift_remain_amount/100 赠送余额,
        t.back_remain_amount/100 返现余额 ,t.create_time 创建时间,t.modify_time 修改时间
        FROM youjia.user_account t
        LEFT JOIN youjia.users u ON t.user_id=u.id
        WHERE t.user_id IS NOT NULL
        AND DATE(t.create_time) BETWEEN DATE_SUB( CURDATE(),INTERVAL 7 day) AND DATE_SUB( CURDATE(),INTERVAL 1 day)
        ORDER BY t.user_id;
    '''
def Cashback():
    return '''
    SELECT concat(r.user_id,'') 用户id,u.realname 姓名,
        CASE r.state WHEN 0 THEN '未返现'
        WHEN 1 THEN '已返现'
        WHEN 2 THEN '无效'
        else '' END 返现状态,
        concat(r.order_id,'') 返现的订单ID,
        CASE r.user_level WHEN  0 THEN  '普通会员'
        WHEN 10 THEN '白银'
        WHEN 20 THEN '黄金'
        WHEN 30 THEN '铂金'
        WHEN 40 THEN '钻石'
        ELSE '' END   当前VIP等级,
        r.back_rate 比例,
        r.predict_back_amount/100 预计返现金额,
        r.actual_back_amount/100 实际返现金额,
        r.create_time 创建时间,
        (
                                    CASE
                                      b.pay_state
                                      WHEN '0'
                                      THEN '待支付'
                                      WHEN '1'
                                      THEN '已支付'
                                      ELSE ''
                                    END
                                  ) 支付状态,
                    (
                                    CASE
                                      b.order_type
                                      WHEN '0'
                                      THEN '分销'
                                      WHEN '1'
                                      THEN '自建'
                                            WHEN '2'
                                      THEN '有家'
                                      ELSE ''
                                    END
                                  ) 订单类型,
                                
                                 CASE b.cancel_status 
                                                WHEN 0  THEN '未取消'
                                        WHEN 1  THEN '房客取消（支付超时）'
                                        WHEN 2 THEN '房客取消（未付款）'
                                        WHEN 3 THEN '房客取消（全额退款）'
                                        WHEN 4 THEN '房客取消（部分扣款）'
                                        WHEN 5 THEN '房客取消（全额扣款）'
                                        WHEN 6 THEN '管家拒绝（订单取消）'
                                        WHEN 7 THEN '管家拒绝（全额退款）'
                                        WHEN 8 THEN '管家拒绝（确认超时）'
                                        WHEN 9 THEN '无房了'
                                        WHEN 10 THEN '客服介入'
                                        else
                                        ''
                                        end 取消状态
        
        
        FROM user_account_back_record r
        LEFT JOIN orders b ON r.order_id=b.id
        LEFT JOIN users u ON r.user_id=u.id
        WHERE r.user_id IS NOT NULL
        AND r.order_id IS NOT NULL
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.order_channel=4
        AND b.city_id<>376
        AND b.brush_status=0
        AND  DATE(r.create_time) BETWEEN DATE_SUB( CURDATE(),INTERVAL 7 day)  AND DATE_SUB( CURDATE(),INTERVAL 1 day);
    '''


def yz_qk():
    return '''
   SELECT
CONCAT(p.id,'') id,
CONCAT(p.parent_id,'') 原欠款id,
CONCAT(p.landlord_id,'') 业主id,u.realname 业主,
CONCAT(p.house_id,'') 房屋id,
p.arrear_amount/100 欠款金额,
p.arrear_reason 欠款原因,
CASE p.arrear_type 
WHEN 1 THEN '软装合同欠款'
WHEN 2 THEN '床垫租金'
WHEN 3 THEN '(软装合同欠款)剩余未还清欠款'
WHEN 4 THEN '(床垫租金)剩余未还清欠款'
WHEN 10 THEN '物业费'
WHEN 11 THEN '取暖费'
WHEN 12 THEN '网费'
WHEN 13 then '租金'
WHEN 14 THEN '筹建房租清算'
WHEN 15 THEN '解除房屋关联'
ELSE '' END 欠款类型,
p.repay_amount/100 还款金额,
concat(p.repay_batch_no,'') 还款结算批次号,
p.residue_Amount/100 剩余欠款金额,
CONCAT(p.create_time,'') 创建时间,
CONCAT(p.modified_time,'') 修改时间,
p.month_time 欠款月份
  FROM youjia_pay.pay_owner_arrear p
LEFT JOIN youjia.users u ON p.landlord_id=u.id
WHERE p.is_delete=0
'''


# 改派订单
def change_order():
    return '''
        SELECT CONCAT(b.id,'') 订单id,CONCAT(b.parent_id,'')   母订单 FROM  youjia.orders b
        WHERE b.brush_status=0
        AND b.special_status=1
        AND b.parent_id <>0
        AND b.city_id<>376
        
        AND DATE(b.create_time) BETWEEN DATE_SUB( CURDATE(),INTERVAL 7 day) and DATE_SUB( CURDATE(),INTERVAL 1 day);

    
    
    '''





if __name__ == '__main__':
    today = datetime.date.today()
    db = get_config_13()
    result_df = pd.read_sql(chongzhi(), con=db) # 充值
    result_df1=pd.read_sql(cost(),con=db) # 消费
    result_df2=pd.read_sql(refund(),con=db) # 退款
    result_df3=pd.read_sql(free(),con=db) #余额
    result_df4=pd.read_sql(Cashback(),con=db) #返现

    result_df5 = pd.read_sql(change_order(), con=db)

    # xlsfile = u'D:/account_' + str(today) + '.xlsx'
    xlsfile = u'/data/online/youjia_script/com/youjia/moban/account_' + str(today) + '.xlsx'
    with pd.ExcelWriter(xlsfile, options={'encoding': 'utf-8'}) as writer:
        # result_df.columns = title
        result_df.to_excel(writer, sheet_name=u'充值', index=False, encoding='utf-8')
        result_df1.to_excel(writer, sheet_name=u'消费', index=False, encoding='utf-8')
        result_df2.to_excel(writer, sheet_name=u'退款', index=False, encoding='utf-8')
        result_df3.to_excel(writer, sheet_name=u'余额', index=False, encoding='utf-8')
        result_df4.to_excel(writer, sheet_name=u'返现', index=False, encoding='utf-8')
        result_df5.to_excel(writer, sheet_name=u'改派订单', index=False, encoding='utf-8')
        writer.save()
        writer.close()

    db.close()

    mail_content = 'Dear all,\n'
    mail_content = mail_content + '    附件为会员本周各项费用及改派订单明细，请查收！[有疑问，联系] \n'
    mail_content = mail_content + '谢谢.'
    sendBigEmail('会员本周各项费用及改派订单' + '_' + today.strftime("%Y-%m-%d"), mail_content, xlsfile,
                 'dt@iyoujia.com;liuxuejiao@city-home.cn')

    # mail_content = 'Dear all,\n'
    # mail_content = mail_content + '    附件为业主欠款各项费用明细，请查收！[有疑问，联系] \n'
    # mail_content = mail_content + '谢谢.'
    # sendBigEmail('业主欠款各项费用明细' + '_' + today.strftime("%Y-%m-%d"), mail_content, xlsfile,
    #              'dt@iyoujia.com;lichunmei@iyoujia.com;')



